﻿Imports System.Data.SqlClient

Public Class ThiCong
    Private sSoTc As String
    Private dNgayLap As Date
    Private sMaDv As String
    Private sSoPhieu As String
    Private iTrangThai As Integer

    Public Property SoTc() As String
        Get
            Return sSoTc
        End Get
        Set(ByVal Value As String)
            sSoTc = Value
        End Set
    End Property

    Public Property NgayLap() As Date
        Get
            Return dNgayLap
        End Get
        Set(ByVal Value As Date)
            dNgayLap = Value
        End Set
    End Property

    Public Property MaDv() As String
        Get
            Return sMaDv
        End Get
        Set(ByVal Value As String)
            sMaDv = Value
        End Set
    End Property

    Public Property SoPhieu() As String
        Get
            Return sSoPhieu
        End Get
        Set(ByVal Value As String)
            sSoPhieu = Value
        End Set
    End Property

    Public Property TrangThai() As Integer
        Get
            Return iTrangThai
        End Get
        Set(ByVal Value As Integer)
            iTrangThai = Value
        End Set
    End Property
End Class

Public Class ThiCongF

    Public Function GetListDonVi() As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_GetListDonVi"

            .Parameters.Add(New SqlClient.SqlParameter("@nam", SqlDbType.Int))
            .Parameters("@nam").Value = Project_Nam

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_Master_All() As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_Get_Master_All"

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_Master_SoTc(ByVal SoTc As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_Get_Master_SoTc"

            .Parameters.Add(New SqlClient.SqlParameter("@sotc", SqlDbType.Char))
            .Parameters("@sotc").Value = SoTc

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_Master_Filter(ByVal MaDv As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_Get_Master_Filter"

            .Parameters.Add(New SqlClient.SqlParameter("@nam", SqlDbType.Int))
            .Parameters("@nam").Value = Project_Nam

            .Parameters.Add(New SqlClient.SqlParameter("@madv", SqlDbType.Char))
            .Parameters("@madv").Value = MaDv
        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function GetLastSoTc() As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_GetLastSoTc"

            .Parameters.Add(New SqlClient.SqlParameter("@nam", SqlDbType.Int))
            .Parameters("@nam").Value = Project_Nam

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function GetDetail(ByVal SoTc As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_GetDetail"

            .Parameters.Add(New SqlClient.SqlParameter("@sotc", SqlDbType.Char))
            .Parameters("@sotc").Value = SoTc

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function ExistsSoTc(ByVal SoTc As String) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_ExistsSoTc"

            .Parameters.Add(New SqlClient.SqlParameter("@sotc", SqlDbType.Char))
            .Parameters("@sotc").Value = SoTc

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function Insert(ByVal ThiCong As ThiCong) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_Insert"

            .Parameters.Add(New SqlClient.SqlParameter("@sotc", SqlDbType.Char))
            .Parameters("@sotc").Value = ThiCong.SoTc

            .Parameters.Add(New SqlClient.SqlParameter("@ngaylap", SqlDbType.SmallDateTime))
            .Parameters("@ngaylap").Value = ThiCong.NgayLap

            .Parameters.Add(New SqlClient.SqlParameter("@madv", SqlDbType.Char))
            .Parameters("@madv").Value = ThiCong.MaDv

            .Parameters.Add(New SqlClient.SqlParameter("@sophieu", SqlDbType.Char))
            .Parameters("@sophieu").Value = ThiCong.SoPhieu

            .Parameters.Add(New SqlClient.SqlParameter("@trangthai", SqlDbType.Int))
            .Parameters("@trangthai").Value = ThiCong.TrangThai

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function Delete(ByVal SoTc As String) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_Delete"

            .Parameters.Add(New SqlClient.SqlParameter("@sotc", SqlDbType.Char))
            .Parameters("@sotc").Value = SoTc

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function GetChietTinh(ByVal maloaichiettinh As String, ByVal tungay As Date, ByVal denngay As Date) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "ThiCong_GetChietTinh"

            .Parameters.Add(New SqlClient.SqlParameter("@maloaichiettinh", SqlDbType.Char))
            .Parameters("@maloaichiettinh").Value = maloaichiettinh

            .Parameters.Add(New SqlClient.SqlParameter("@tungay", SqlDbType.SmallDateTime))
            .Parameters("@tungay").Value = tungay

            .Parameters.Add(New SqlClient.SqlParameter("@denngay", SqlDbType.SmallDateTime))
            .Parameters("@denngay").Value = denngay

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

End Class

